import re
import openpyxl

def load_translations(excel_file):
    """从Excel文件中加载翻译，返回一个字典，键为中文，值为英文"""
    translations = {}
    wb = openpyxl.load_workbook(excel_file)
    ws = wb['待翻译字符串']  # 假设工作表名为“待翻译字符串”
    
    for row in ws.iter_rows(min_row=2, values_only=True):  # 跳过表头
        chinese_text = row[1]  # 原始字符串(中文) 在第二列
        english_text = row[2]  # 翻译后的字符串(英文) 在第三列
        if chinese_text and english_text:
            translations[chinese_text] = english_text
    
    return translations

def replace_chinese_with_english(txt_file, output_file, translations):
    """读取txt文件，替换中文为英文，并写入到新文件中"""
    with open(txt_file, 'r', encoding='utf-8') as file:
        content = file.read()
    
    # 使用正则表达式匹配所有双引号内的内容，并尝试替换
    def replace_match(match):
        original_text = match.group(0)  # 匹配到的整个字符串，包括引号
        inner_text = match.group(1)  # 匹配到的引号内的内容
        if inner_text in translations:
            return f'"{translations[inner_text]}"'  # 替换为英文，并保留引号
        return original_text  # 如果没有找到翻译，则保留原样
    
    pattern = re.compile(r'"(.*?)"')
    new_content = pattern.sub(replace_match, content)
    
    with open(output_file, 'w', encoding='utf-8') as file:
        file.write(new_content)

if __name__ == "__main__":
    excel_file = r"translation_strings.xlsx"
    txt_file = r"sourceData.txt"
    output_file = r"translated_sourceData.txt"  # 新的输出文件路径
    
    translations = load_translations(excel_file)
    replace_chinese_with_english(txt_file, output_file, translations)
    print("替换完成并写入到新文件！")